package net.tycmc.cty.ctyms.timejob.iems.dao;

import java.util.List;
import java.util.Map;


import com.tycmc.functions.util.MapGetter;
import net.tycmc.bulb.common.dao.IBaseDAO;
import net.tycmc.bulb.common.dao.ITransactionDAO;
import net.tycmc.bulb.common.util.DateUtil;
import net.tycmc.bulb.common.util.StringUtil;
import net.tycmc.bulb.dbutil.global.GlobalDBVar;
import net.tycmc.cty.ctyms.timejob.common.ConfigEnum;
import net.tycmc.cty.ctyms.timejob.common.DateRegUtil;
import net.tycmc.cty.ctyms.timejob.common.MyConnectDSDAO;
import net.tycmc.cty.ctyms.timejob.common.MyConnectDSTransDAO;

public class EffectivenessStatDAO {
	private MyConnectDSDAO mcdsDAO = new MyConnectDSDAO();
	private MyConnectDSTransDAO mcdstDAO = new MyConnectDSTransDAO();
	private IBaseDAO baseDAO;
	public void setBaseDAO(IBaseDAO baseDAO) {
		this.baseDAO = baseDAO;
	}
	/**
	 * 获取进出围栏提示表设备为JPP设备的所有数据，按照设备ID、时间均正序排序
	 * 
	 **/
	public List<Map<String, Object>> queryFenTipInfo() {
		StringBuffer strBuf =new StringBuffer();
		strBuf.append(
				"select " +
				"maf.MsgAF_ID," +
				"maf.MsgAF_Vcl_ID," +
				"maf.MsgAF_Longitude," +
				"MsgAF_Latitude, "+
				"maf.MsgAF_Tmileage,"+
				"maf.MsgAF_tRunKm_ECM,"+
				"maf.MsgAF_tRunKm_ECU,"+
				"maf.MsgAF_tRunKmExact_ECM,"+
				"maf.MsgAF_tRunKmExact,"+
				"maf.MsgAF_tOilCons,"+
				"maf.MsgAF_tWorkTime_UDS,"+
				"maf.MsgAF_FenceID,"+
				"maf.MsgAF_AlarmTime,"+
				"maf.MsgAF_AlarmType,"+
				"vw.VclPS_isOpen " +
				" from "+GlobalDBVar.getDBFullName_Conn("Msg_Alarm_Fence_"+DateUtil.addDay(-1).replaceAll("-",""), "4", DateUtil.addDay(-1)+" 00:00:00") +"  maf ");
		strBuf.append(" inner join EffOLine e on e.EffOL_VclID = maf.MsgAF_Vcl_ID and e.EffOL_FenceID = maf.MsgAF_FenceID ");
		strBuf.append(" left join " +GlobalDBVar.getDBFullName_Conn("VclPromptSwitch","1",null)+" vw "+
				" on maf.MsgAF_Vcl_ID=vw.VclPS_VclID and vw.VclPS_Type='7'");
		strBuf.append(" order by maf.MsgAF_Vcl_ID ,maf.MsgAF_AlarmTime ");
		return this.baseDAO.execQuery(strBuf.toString(), new Object[]{});
	}

	/**
	 * 最后一条出围栏提示信息录入未处理围栏表
	 * 
	 **/
	public boolean InsertWaitDealPassFT(String vehicleID, String fenTipId,String msgTime,String isbakTable) {
		String sql = "insert into WaitDealPassFT(WDPassFT_VclID,WDPassFT_PassFT_ID,WDPassFT_msgTime)values(?,?,?)";
		int flag = this.mcdsDAO.execInsert(sql, new Object[]{vehicleID,fenTipId,msgTime});
		//二期优化：是否需要数据备份
		if(isbakTable.equals("1")){
			MyConnectDSDAO conn=new MyConnectDSDAO(true);
			int flager=conn.execInsert(sql, new Object[]{vehicleID,fenTipId,msgTime});
			if(flager<=0){
				System.out.println("最后一条除蔚蓝提示信息录入未处理围栏表备份失败！！");
			}
		}
		if(flag>0){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 按指定设备，查询此设备是否存在未处理围栏信息
	 * 
	 **/
	public Map<String, Object> queryWaitDealInfoByVehicleId(String vehicleID) {
		StringBuffer strBuf =new StringBuffer();
		strBuf.append("select top 1 * from WaitDealPassFT wf ");
		strBuf.append("where  WDPassFT_VclID=? and WDPassFT_msgTime is not null and WDPassFT_msgTime>='2015-01-13' order by WDPassFT_msgTime desc");
		return this.baseDAO.execQueryOne(strBuf.toString(), new Object[]{vehicleID});
	}
	
	/**
	 * 按指定信息ID，查询进出围栏提示信息表其他信息
	 *
	 **/
	public Map<String, Object> queryFenTipInfoById(String fenId,String vehicleID,String MsgTime) {
		StringBuffer strBuf =new StringBuffer();
		String time=MsgTime.substring(0,10).replaceAll("-","");
		strBuf.append("select " +
				"maf.MsgAF_ID," +
				"maf.MsgAF_Vcl_ID," +
				"maf.MsgAF_Longitude," +
				"MsgAF_Latitude, "+
				"maf.MsgAF_Tmileage,"+
				"maf.MsgAF_tRunKm_ECM,"+
				"maf.MsgAF_tRunKm_ECU,"+
				"maf.MsgAF_tRunKmExact_ECM,"+
				"maf.MsgAF_tRunKmExact,"+
				"maf.MsgAF_tOilCons,"+
				"maf.MsgAF_tWorkTime_UDS,"+
				"maf.MsgAF_FenceID,"+
				"maf.MsgAF_AlarmTime,"+
				"maf.MsgAF_AlarmType "+
				"from "+GlobalDBVar.getDBFullName_Conn("Msg_Alarm_Fence_"+time, "4", MsgTime)+" maf ");
		strBuf.append("where  MsgAF_ID=? and MsgAF_Vcl_ID = ? ");
		return this.baseDAO.execQueryOne(strBuf.toString(), new Object[]{fenId,vehicleID});
	}
	
	/**
	 * 按指定设备ID，查询设备相关信息
	 * 
	 **/
	public Map<String, Object> queryInfoByVehicleId(String vehicleID) {
		StringBuffer strBuf =new StringBuffer();
		strBuf.append("select VehicleID,operLineName,VehicleRumKmID from V_VehicleInfo where VehicleID = ?");
		Map<String, Object> map = this.baseDAO.execQueryOne(strBuf.toString(), new Object[]{vehicleID});
		return map;
	}
	/**
	 * 按指定围栏ID，查询查询对应的序号
	 * 
	 **/
	public  int queryEffNumByFenId(String fenID,String Vehicleid) {
		StringBuffer strBuf =new StringBuffer();
		String effNum = "";
		strBuf.append("select * from EffOLine where EffOL_FenceID = ? and EffOL_VclID = ?");
		Map<String, Object> map = this.baseDAO.execQueryOne(strBuf.toString(), new Object[]{fenID,Vehicleid});
		effNum = MapGetter.getString(map, "EffOL_Num");
		if(effNum.length()>0){
			return Integer.parseInt(effNum);
		} else {
			return 0;
		}
	}
	/**
	 * 获取两个序号之间的规定时间
	 * 
	 **/
	public  double getEffTimeTotal(int beNum,int edNum,String vehicleID) {
		StringBuffer strBuf =new StringBuffer();
		strBuf.append("select SUM(EffRT_RuleTime) as time from EffRuleTime ");
		strBuf.append("where EffRT_Num >= ? and EffRT_Num <= ? and EffRT_VclID = ? group by EffRT_VclID");
		Map<String, Object> map = this.baseDAO.execQueryOne(strBuf.toString(), new Object[]{beNum,edNum,vehicleID});
		Double time  = MapGetter.getDouble(map, "time");
		return time;
	}
	/**
	 * 根据进出围栏提示ID，删除未处理围栏表信息
	 * 
	 **/
	public  boolean deleteWaitDealPassFTById(String fenID,String isbakTable) {
		StringBuffer strBuf =new StringBuffer();
		strBuf.append("delete from WaitDealPassFT where WDPassFT_PassFT_ID = ? ");
		int flag = this.mcdsDAO.execUpdate(strBuf.toString(), new Object[]{fenID});
		//二期优化：是否需要数据备份
		if(isbakTable.equals("1")){
			MyConnectDSDAO conn=new MyConnectDSDAO(true);
			int flager = conn.execUpdate(strBuf.toString(), new Object[]{fenID});
			if(flager<=0){
				System.out.println("删除未处理围栏表信息备份失败！！");
			}
		}
		if(flag>0){
			return true;
		}else{
			return false;
		}
	}
	
	
	/**
	 * 查询量围栏开始围栏载重
	 * 
	 **/
	public Map<String, Object> queryBeLoad(String vehicleID,String beTime,String endTime) {
		StringBuffer strBuf =new StringBuffer();
		String LoadMsgsql=SpellLoadMsgTable(beTime,endTime);
		strBuf.append("select top 1 * from ("+LoadMsgsql+") ml where MsgESLS_IntervalBeginTime<? and msgesls_vcl_id = ? and MsgESLS_AverageLoad>=0 order by MsgESLS_IntervalBeginTime desc");
		Map<String, Object> map = this.baseDAO.execQueryOne(strBuf.toString(), new Object[]{beTime,vehicleID});
		return map;
	}
	
	/**
	 * 查询量围栏结束围栏载重
	 * 
	 **/
	public Map<String, Object> queryEdLoad(String vehicleID,String edTime) {
		StringBuffer strBuf =new StringBuffer();
		String LoadMsgsql=SpellLoadMsgTable(edTime,edTime);
		strBuf.append("select top 1 * from ("+LoadMsgsql+") ml where MsgESLS_IntervalEndTime>? and msgesls_vcl_id = ? and MsgESLS_AverageLoad>=0 order by MsgESLS_IntervalEndTime asc");
		Map<String, Object> map = this.baseDAO.execQueryOne(strBuf.toString(), new Object[]{edTime,vehicleID});
		return map;
	}
	
	/**
	 * 查询围栏间的载重所有区间段
	 * @param beMap
	 * @param edMap
	 * @param vehicleID
	 * @return
	 */
	public List<Map<String, Object>> queryBwLoad(Map<String, Object> beMap,Map<String, Object> edMap,String vehicleID){
		String loadStatSql=SpellLoadMsgTable(MapGetter.getString(beMap, "MsgESLS_IntervalBeginTime"),MapGetter.getString(edMap, "MsgESLS_IntervalEndTime"));
		StringBuffer strBuf =new StringBuffer();
		strBuf.append("select * from ("+loadStatSql+") ml where MsgESLS_IntervalBeginTime >=? and MsgESLS_IntervalEndTime<=? and MsgESLS_Vcl_ID = ? and MsgESLS_AverageLoad>=0 ");
		List<Map<String, Object>> Listmap = this.baseDAO.execQuery(strBuf.toString(), 
				new Object[]{MapGetter.getString(beMap, "MsgESLS_IntervalBeginTime"),
			                 MapGetter.getString(edMap, "MsgESLS_IntervalEndTime"),vehicleID});
		return Listmap;
		
	}
	/**
	 * 批量执行数据的插入
	 * 
	 **/
	public  boolean insertAllToTable(List<String> sqlList,List<Object[]> paramList,String isbakTable) {
		boolean isOk = this.mcdstDAO.batch(sqlList, paramList);
		//二期优化：是否需要数据备份
		if(isbakTable.equals("1")){
			MyConnectDSTransDAO connDAO=new MyConnectDSTransDAO(true);
			boolean flager=connDAO.batch(sqlList,paramList);
			if(!flager){
				System.out.println("批量执行数据备份插入失败！！");
			}
		}
		return isOk;

	}
	/**
	 * sxh 2014 2.5
	 * 载重统计表拼写
	 * */
	public String  SpellLoadMsgTable(String begtime,String endtime){
		List<String> list=DateRegUtil.iterateTime(begtime,endtime);
		String sql="";
		for(String time:list){
			time=time.replaceAll("-","");
			 sql+="select MsgESLS_Vcl_ID,"+
					 	"MsgESLS_LoadType,"+
						"MsgESLS_AverageLoad,"+
						"MsgESLS_IntervalFuel ,"+
						"MsgESLS_IntervalWorkTime,"+
						"MsgESLS_IntervalRunKmExact,"+
						"MsgESLS_IntervalRunKmECM,"+
						"MsgESLS_IntervalRunKmECU,"+
						"MsgESLS_IntervalRunKmExact_PGN,"+
						"MsgESLS_IntervalBeginTime,"+
						"MsgESLS_IntervalEndTime,"+
						"MsgESLS_IntervalTMileage "+
						"from "+GlobalDBVar.getDBFullName_Conn("Msg_EquipmentState_LoadStatistics_"+time, "3", null)+" union all ";
		}
		if(sql.length()>10){
			sql=sql.substring(0,sql.length()-10);
		}
		return sql;
	}
	public void deleteEffect() {
		String sql = "delete EffState where EffS_StateTime=GETDATE()";
		this.baseDAO.execUpdate(sql, new Object[]{});
		
	}
}